﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for PostsDAL
/// </summary>
public class PostsDAL: StandardConnectDB, IPosts
{
	public PostsDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public List<Posts> GetListPostsOfAllUser()
    {
        List<Posts> listpost = new List<Posts>();
        DataTable dt = this.DTExecuteSelect("SELECT * FROM Posts");
        foreach (DataRow dr in dt.Rows)
        {
            listpost.Add(this.ConvertObjectDataRowToPosts(dr));
        }
        return listpost;

    }

    public List<Posts> GetListPostsOfUser(Users user)
    {
        //Create object listPosts
        List<Posts> listPosts = new List<Posts>();

        //Create object paramList store parameter execute store procedure
        Dictionary<String, Object> paramList = new Dictionary<String, Object>();

        //Add parameter pUsername to paramList
        paramList.Add("@pUsername", user.Username);

        //Get Posts by store procedure
        DataTable dt = this.DTExecuteSelect("procGetListPostsOfUserByUsername", paramList);

        //Add Posts from DataTable to listPosts
        foreach (DataRow dr in dt.Rows)
        {
            //Convert objec DataRow to object Posts and add to listPosts
            listPosts.Add(this.ConvertObjectDataRowToPosts(dr));
        }

        return listPosts;
    }

    public List<Posts> GetListPostOfUserByDate(Users user, DateTime date)
    {
        //Create object listPosts
        List<Posts> listPosts = new List<Posts>();

        //Create object paramList store parameter execute store procedure
        Dictionary<String, Object> paramList = new Dictionary<String, Object>();

        //Add parameter pUsername to paramList
        paramList.Add("@pUsername", user.Username);
        paramList.Add("@pCreateTime", date);

        //Get Posts by store procedure
        DataTable dt = this.DTExecuteSelect("procGetListPostOfUserByDate", paramList);

        //Add Posts from DataTable to listPosts
        foreach (DataRow dr in dt.Rows)
        {
            //Convert objec DataRow to object Posts and add to listPosts
            listPosts.Add(this.ConvertObjectDataRowToPosts(dr));
        }

        return listPosts;
    }

    public List<Posts> GetPostByPrivacy(Users user, bool privacy)
    {
        //Create object listPosts
        List<Posts> listPosts = new List<Posts>();

        //Create object paramList store parameter execute store procedure
        Dictionary<String, Object> paramList = new Dictionary<String, Object>();

        //Add parameter pUsername to paramList
        paramList.Add("@pUsername", user.Username);
        paramList.Add("@pPrivacy", privacy);

        //Get Posts by store procedure
        DataTable dt = this.DTExecuteSelect("procGetListPostOfUserByPrivacy", paramList);

        //Add Posts from DataTable to listPosts
        foreach (DataRow dr in dt.Rows)
        {
            //Convert objec DataRow to object Posts and add to listPosts
            listPosts.Add(this.ConvertObjectDataRowToPosts(dr));
        }

        return listPosts;
    }
    public Posts GetPostByIdPost(int idPost)
    {
        //Create object listPosts
        Posts post = new Posts();

        //Create object paramList store parameter execute store procedure
        Dictionary<String, Object> paramList = new Dictionary<String, Object>();

        //Add param to list

        paramList.Add("@pIdPost", idPost);

        //Get Posts by store procedure
        DataTable dt = this.DTExecuteSelect("procGetPostByIdPost", paramList);

        //Convert object DataRow to object Posts and add to listPosts
        post = this.ConvertObjectDataRowToPosts(dt.Rows[0]);

        return post;
    }

    public Posts GetPostByIdPost(Users user, int idPost)
    {
        //Create object listPosts
        Posts post = new Posts();

        //Create object paramList store parameter execute store procedure
        Dictionary<String, Object> paramList = new Dictionary<String, Object>();

        //Add param to list
        paramList.Add("@pUsername", user.Username);
        paramList.Add("@pIdPost", idPost);

        //Get Posts by store procedure
        DataTable dt = this.DTExecuteSelect("procGetPostOfUserByIdPost", paramList);

        //Convert object DataRow to object Posts and add to listPosts
        post = this.ConvertObjectDataRowToPosts(dt.Rows[0]);

        return post;
    }

    public bool CreatePost(Users user, string contents, bool privacy)
    {
        //Create object result
        bool result = false;

        //Create param list store parameter execute store procedure
        Dictionary<string, object> paramList = new Dictionary<string, object>();

        //Add param to list
        paramList.Add("@pUsername", user.Username);
        paramList.Add("@pContents", contents);
        paramList.Add("@pPrivacy", privacy);

        //Insert object Posts to table Posts in database by store procedure
        result = this.ExecuteUpdate("procCreatePost", paramList);

        return result;
    }

    public bool DeletePost(Posts posts)
    {
        //Create object result
        bool result = false;

        //Create param list store parameter execute store procedure
        Dictionary<string, object> paramList = new Dictionary<string, object>();

        //Add param to list
        paramList.Add("@pIdPost", posts.IdPost);

        //Update object Posts by store procedure
        result = this.ExecuteUpdate("procDeletePost", paramList);

        return result;
    }

    public bool UpdatePost(Posts posts)
    {
        throw new NotImplementedException();
    }

    public Posts ConvertObjectDataRowToPosts(System.Data.DataRow dr)
    {
        //Create object Posts
        Posts post = new Posts();

        //Set properties of Posts
        post.IdPost = int.Parse(dr["IdPost"].ToString());
        post.Contents = dr["Contents"].ToString();
        post.Privacy = bool.Parse(dr["Privacy"].ToString());
        post.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());

        //Get User
        string user = dr["Username"].ToString();
        //Create object IUsers
        IUsers iUser = new UsersDAL();
        //Set property User of Posts
        post.User = iUser.GetUserByUsername(user);

        return post;
    }


    public DataTable GetDataTablePostsOfUserAndFriends()
    {
        DataTable dt = this.DTExecuteSelect("SELECT up.Avatar, u.Username, p.Contents, p.IdPost FROM Users u JOIN UserProfiles up ON u.UserName = up.Username JOIN Posts p ON u.UserName = p.Username ORDER BY p.IdPost desc");
        
        return dt;
    }
}